package com.kcs.dc.report.model.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.SortedMap;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.LogWriter;
import com.kcs.common.utils.PropsUtils;
import com.kcs.common.utils.commonUtils;
import com.kcs.dc.report.model.dao.model.ReportTypeModel;
import com.kcs.dc.report.model.dao.model.utilGridInfoModel;

public class reportDAO {
	
	public ReportTypeModel[] selReportType(String userIdSession, Connection conn, String menu_scode) throws SQLException {
		
		CallableStatement proc = null;
		ResultSet rs = null;
		ReportTypeModel model = null;
		ReportTypeModel[] models = null;
		ArrayList<ReportTypeModel> arrList = new ArrayList<ReportTypeModel>();
		
		try {
			proc = conn.prepareCall("{ call qry_menu_child_proc(?, ?, ?) }");
			proc.setString(1, userIdSession);
			proc.setString(2, PropsUtils.getMessage(Constant.PROP_OTHER, menu_scode));  
			proc.registerOutParameter(3, Types.INTEGER);
			
			rs = proc.executeQuery();
			while (rs.next()) {
				model = new ReportTypeModel();
				model.setValue(rs.getString("menu_shtcode"));
				model.setDisplay(rs.getString("menu_name"));
				
				arrList.add(model);
			}
			
			if (arrList.size() > 0) {
				models = (ReportTypeModel[]) arrList.toArray(new ReportTypeModel[arrList.size()]);
				return models;
			}
					
			
		} catch (Exception e) {
			LogWriter.error("1.1 reportDAO [Method : selReportType call Store qry_menu_child_proc] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
			throw new SQLException();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 reportDAO [Method : selReportType call Store qry_menu_child_proc] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 reportDAO [Method : selReportType call Store qry_menu_child_proc] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
		}
		return models;
	}		
	
	public utilGridInfoModel getReportInfomation(String rep_Type,
			String date_from, String date_to, String ibccode, String custname,
			String deal_id, String trader_name, String proxy_group,
			String proxy_name, String costcenter, Connection conn) throws SQLException {
		
		CallableStatement proc = null;
		ResultSet rs = null;
		Result result = null;
		String[][] detail = null;
		utilGridInfoModel r_model = null;
		
		try {
			proc = conn.prepareCall("{ call rep_utilized_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
			
			proc.setString(1, rep_Type);
			proc.setString(2, date_from); 
			proc.setString(3, date_to);
			proc.setString(4, ibccode); 
			proc.setString(5, custname);
			proc.setString(6, deal_id);
			proc.setString(7, trader_name); 
			proc.setString(8, proxy_group);
			proc.setString(9, proxy_name);
			proc.setString(10, costcenter);
			proc.registerOutParameter(11, Types.INTEGER);
			
			rs = proc.executeQuery();
			result = ResultSupport.toResult(rs);
			int column = result.getColumnNames().length;
			int row = result.getRowCount();			
			SortedMap[] rows = result.getRows();
			
			/*Loop set value to String array dimension. */
			r_model = new utilGridInfoModel();
			r_model.setColumn(column);
			r_model.setColunmName(result.getColumnNames());
			if (row > 0){
				// Have result.
				detail = new String[row][column]; 
				for (int r = 0; r < row; r++) {	
					SortedMap r_row = rows[r];
					for (int c = 0; c < column; c++) {
						if(result.getColumnNames()[c].equals("total_settle_amount") ||
								result.getColumnNames()[c].equals("reverse_amount") ||
								result.getColumnNames()[c].equals("settle_amount") ||
								result.getColumnNames()[c].equals("total_left_utilized_amount")){
							detail[r][c] = commonUtils.formatNumber((Double) r_row.get(result.getColumnNames()[c]), 2, "0",true);						
							
						} else if (result.getColumnNames()[c].equals("exch_rate")){
							detail[r][c] = commonUtils.formatNumber((Double) r_row.get(result.getColumnNames()[c]), 8, "#",true);
						} else {
							detail[r][c] = r_row.get(result.getColumnNames()[c]).toString();
						}
					}				
				}			
			
			r_model.setRow(row);
			r_model.setGridInfo(detail);
				
			}	
				
		} catch (SQLException e) {
			LogWriter.error("1.1 reportDAO [Method : getReportInfomation] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
			throw new SQLException();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 reportDAO [Method : getReportInfomation] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 reportDAO [Method : getReportInfomation] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
		}
		return r_model;
	}
	
	public utilGridInfoModel getLogDealCenter(String rep_Type,
			String log_date_from, String log_date_to, String system, String service,
			String deal_id, String src_ref, String costcenter_id, String val_date_from,
			String val_date_to, Connection conn) throws SQLException {
		
		CallableStatement proc = null;
		ResultSet rs = null;
		Result result = null;
		String[][] detail = null;
		utilGridInfoModel r_model = null;
		
		try {
			proc = conn.prepareCall("{ call rep_mornitor_log_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
			
			proc.setString(1, rep_Type);
			proc.setString(2, log_date_from); 
			proc.setString(3, log_date_to);
			proc.setString(4, system); 
			proc.setString(5, service);
			proc.setString(6, deal_id);
			proc.setString(7, src_ref); 
			proc.setString(8, costcenter_id);
			proc.setString(9, val_date_from);
			proc.setString(10, val_date_to);
			proc.registerOutParameter(11, Types.INTEGER);
			
			rs = proc.executeQuery();
			result = ResultSupport.toResult(rs);
			int column = result.getColumnNames().length;
			int row = result.getRowCount();			
			SortedMap[] rows = result.getRows();
			
			/*Loop set value to String array dimension. */
			r_model = new utilGridInfoModel();
			r_model.setColumn(column);
			r_model.setColunmName(result.getColumnNames());
			if (row > 0){
				// Have result.
				detail = new String[row][column]; 
				for (int r = 0; r < row; r++) {	
					SortedMap r_row = rows[r];
					for (int c = 0; c < column; c++) {
						if(result.getColumnNames()[c].equals("outstanding") ||
								result.getColumnNames()[c].equals("scr_settle_amount")){
							detail[r][c] = commonUtils.formatNumber((Double) r_row.get(result.getColumnNames()[c]), 2, "0",true);						
							
						}  else {
							detail[r][c] = r_row.get(result.getColumnNames()[c]).toString();
						}
					}				
				}			
			
			r_model.setRow(row);
			r_model.setGridInfo(detail);
				
			}	
				
		} catch (SQLException e) {
			LogWriter.error("1.1 reportDAO [Method : getReportInfomation] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
			throw new SQLException();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 reportDAO [Method : getReportInfomation] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 reportDAO [Method : getReportInfomation] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
		}
		return r_model;
	}
	
}
